package com.linktco.common.utils.excel.handle;

import com.alibaba.fastjson.JSON;
import com.linktco.common.utils.U;
import com.linktco.common.utils.excel.annotation.AnalysisUtils;
import com.linktco.common.utils.excel.annotation.Excel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import javax.sql.rowset.serial.SerialException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 执行导入操作
 *
 * @author zhangnx
 */
@Slf4j
public class ImportExcel<T> {

    private final DataFormatter dataFormatter = new DataFormatter();

    private final String E ="E";

    @ApiModelProperty("统计发生错误的次数")
    private Integer errorCount;

    @ApiModelProperty("发生的错误记录")
    private List<String> errorRecord;

    @ApiModelProperty("实体对象")
    private Class<T> clazz;

    @ApiModelProperty("文件中标题的行数")
    private Integer titleRowNum;

    @ApiModelProperty("导入数据的缓存大小")
    private Integer cacheSize;

    @ApiModelProperty("连续保存数据失败次数阈值")
    private Integer saveFailThreshold;

    @ApiModelProperty("导入数据的缓存集合")
    private List<T> cacheList;

    @ApiModelProperty("工作薄对象")
    private Workbook wb;

    @ApiModelProperty("工作表对象")
    private Sheet sheet;

    @ApiModelProperty("数据导入的钩子")
    private ImportHandler<T> handler;

    public ImportExcel(InputStream is, Class<T> clazz, String sheetName, Integer titleRowNum, Integer cacheSize, Integer saveFailThreshold, ImportHandler handler) throws IOException, InvalidFormatException {
        init(clazz, is, sheetName, titleRowNum, cacheSize, saveFailThreshold, handler);
    }


    /**
     * 初始化参数
     *
     * @param clazz             导入数据类型类对象
     * @param is                输入流
     * @param sheetName         sheetName
     * @param titleRowNum       标题最大行号
     * @param cacheSize         集合缓存大小
     * @param saveFailThreshold 连续保存数据失败次数阈值
     * @param handler           导入回调钩子
     * @throws IOException
     * @throws InvalidFormatException
     */
    private void init(Class<T> clazz, InputStream is, String sheetName, Integer titleRowNum, Integer cacheSize, Integer saveFailThreshold, ImportHandler handler) throws IOException, InvalidFormatException {
        this.errorCount = 0;
        this.errorRecord = new ArrayList<>();
        this.clazz = clazz;
        this.titleRowNum = titleRowNum == null ? 1 : titleRowNum;
        this.wb = WorkbookFactory.create(is);
        if (StringUtils.isNotEmpty(sheetName)) {
            this.sheet = this.wb.getSheet(sheetName);
        } else {
            this.sheet = this.wb.getSheetAt(0);
        }
        this.cacheSize = cacheSize == null ? 100 : cacheSize;
        this.saveFailThreshold = saveFailThreshold == null ? 5 : saveFailThreshold;
        this.cacheList = new ArrayList<>();
        this.handler = handler;
    }






    /**
     * 封装批量保存的回调
     *
     * @param handler              回调钩子
     * @param cacheList            数据缓存集合
     * @param saveCallBackErrorNum 保存回调失败次数
     */
    private int batchSaveCallBack(ImportHandler<T> handler, List<T> cacheList, int saveCallBackErrorNum) {
        int size = cacheList.size();
//        try {
            handler.saveCallBack(cacheList);
            cacheList.clear();
// saveCallBackErrorNum = 0;
//        } catch (Exception e) {
//            log.error("==> 批量保存数据时出错了，错误原因:{}", e.getMessage());
//            saveCallBackErrorNum++;
//            if (saveCallBackErrorNum == this.saveFailThreshold) {
//                log.error("==> 连续{}次批量保存数据出错，结束导入操作...", this.saveFailThreshold);
//                throw new RuntimeException(e.getMessage());
//            } else {
//                size = this.batchSaveCallBack(handler, cacheList, saveCallBackErrorNum);
//            }
//        }
        return size;
    }




    /**
     * 执行导入操作
     */
    public ImportResult execute() {
        log.info("==> 开始执行导入操作...");
        if (this.sheet == null) {
            throw new RuntimeException("无法获取sheet对象。");
        }

        ImportResult result = new ImportResult();
        result.setCode(ImportResult.Type.ERROR);

        List<ExcelField> titleFields = new ArrayList<>();
        titleFields = getTitleFields(titleFields, this.titleRowNum - 1);

        //获取总行数
        int totalRowNum = this.sheet.getPhysicalNumberOfRows();
        for (int i = this.titleRowNum; i < totalRowNum; i++) {
            T vo = getNewInstance();
            Boolean flag = this.columnHandle(i, vo, titleFields, result);

            T rowVo = handler.rowCallBack(i + 1, this.sheet.getRow(i), vo);
            if (flag) {
                this.cacheList.add(rowVo!=null?rowVo:vo);
                result.addSuccessRow(i + 1);
            }

            if (this.cacheSize <= this.cacheList.size() || totalRowNum == i + 1) {
                int size = this.batchSaveCallBack(this.handler, this.cacheList, 0);
                result.setSuccessCount(result.getSuccessCount() + size);
            }
        }

        log.info("=====> 导出操作执行完成。");

        result.setErrorCount(this.errorCount);
        result.setErrorRecord(this.errorRecord);
        if (!result.getSuccessRow().isEmpty()) {
            if (result.getSuccessRow().size() == totalRowNum - this.titleRowNum) {
                result.setCode(ImportResult.Type.SUCCESS);
            } else {
                result.setCode(ImportResult.Type.PART_PASS);
            }
        }

        return result;
    }




    @SneakyThrows
    private Boolean columnHandle(Integer iRow, T vo, List<ExcelField> titleFields, ImportResult result) {
        //获取列数
        Row row = this.sheet.getRow(iRow);
        int columns = row.getLastCellNum();
        for (int i = 0; i < columns; i++) {
            //获取单元格的原始值
            Cell cell = row.getCell(i);
//            try {
                if (i>titleFields.size()-1){
                    throw new RuntimeException(String.format("Excel模板错误,系统定义的表头为%s行,%s列，Excel的列数为：%s",this.titleRowNum,titleFields.size(),columns));
                }
                Object value = null;
                Field field = titleFields.get(i).getField();
                if (U.notEmpty(cell)) {
                    value = this.getExcelDateByIndex(this.sheet, iRow, i, cell.getCellTypeEnum(), field);
                }
                Object columnCallBackValue = this.handler.columnCallBack(iRow + 1, i + 1, value);
                if (columnCallBackValue != null) {
                    value = columnCallBackValue;
                }

                field.setAccessible(true);
                field.set(vo, value);
                field.setAccessible(false);

//            } catch (Exception e) {
//                String errorMsg = String.format("导入第%s行第%s列数据时出错了，错误原因：%s", iRow + 1, i + 1, e.getMessage());
//                log.error(errorMsg);
//                this.handler.errorCallBack(iRow + 1, i + 1, cell,errorMsg);
//                this.errorCount++;
//                this.errorRecord.add(errorMsg);
//                result.addErrorRow(iRow + 1);
//                return false;
//            }
        }
        return true;
    }




    /**
     * 获取表头列
     *
     * @param excelFields
     * @param titleRowNum
     * @return
     */
    private List<ExcelField> getTitleFields(List<ExcelField> excelFields, Integer titleRowNum) {
        //获取列数
        Row titleRow = this.sheet.getRow(titleRowNum);
        int titleColumns = titleRow.getLastCellNum();
        Field[] clazzFields = clazz.getDeclaredFields();

        List<Integer> resultColumns = excelFields.stream().map(ExcelField::getColumn).collect(Collectors.toList());
        for (int i = 0; i < titleColumns; i++) {
            if (resultColumns.contains(i)) {
                continue;
            }

            //获取单元格的原始值
            Cell cell = titleRow.getCell(i);
            if (U.notEmpty(cell)) {
                Object value = this.getExcelDateByIndex(this.sheet, titleRowNum, i, cell.getCellTypeEnum(), null);

                for (Field field : clazzFields) {
                    Excel attr = field.getAnnotation(Excel.class);
                    if (attr == null) {
                        continue;
                    }
                    String columnName = U.isEmpty(attr.name())?field.getName():attr.name();
                    columnName = columnName.substring(columnName.lastIndexOf("^") + 1);
                    if (value.toString().equals(columnName)) {
                        ExcelField excelField = new ExcelField(i, field);
                        excelFields.add(excelField);
                        break;
                    }
                }
            }
        }

        if (excelFields.size() < titleColumns && titleRowNum>=1) {
            getTitleFields(excelFields, titleRowNum - 1);
        }

        return excelFields.stream().sorted(Comparator.comparing(field -> field.getColumn())).collect(Collectors.toList());
    }




    /**
     * 根据行和列的索引获取单元格的值
     *
     * @param sheet        sheet对象
     * @param rowNum       行索引
     * @param columnNum    列索引
     * @param cellTypeEnum 单元格数据类型
     * @param field
     * @return
     */
    @SneakyThrows
    private Object getExcelDateByIndex(Sheet sheet, int rowNum, int columnNum, CellType cellTypeEnum, Field field) {

        Cell cell = sheet.getRow(rowNum).getCell(columnNum);
        Object result = null;
        if (CellType.NUMERIC == cellTypeEnum) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                result = cell.getDateCellValue();
            }else {
                double value = cell.getNumericCellValue();
                if (Double.toString(value).contains(E)) {
                    result = dataFormatter.formatCellValue(cell);
                }else {
                    result = cell.getNumericCellValue();
                }
            }

        } else if (CellType.STRING == cellTypeEnum) {
            result = cell.getStringCellValue();

        } else if (CellType.FORMULA == cellTypeEnum) {
            result = cell.getCellFormula();

        } else if (CellType.BOOLEAN == cellTypeEnum) {
            result = cell.getBooleanCellValue();
        }

        if (field ==null || result == null){return result;}
        if (result instanceof Date){ return result; }

        result = AnalysisUtils.importConvertExp(field,result);

        if (!field.getType().equals(result.getClass())){
            try {
                result = JSON.parseObject(result.toString(), field.getType());
            }catch (Exception e){
                String format = String.format("第%s行%s列%s转%s(%s)失败!", rowNum, columnNum, result, field.getName(), field.getType().getName());
                throw new Exception(format);
            }
        }
        return result;
    }




    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     *
     * @param sheet         sheet对象
     * @param cellValue     当前单元格的值
     * @param currentColumn 当前单元格列的索引
     * @param targetColumn  目标单元格列的索引
     * @return
     */
    private String getCellByCaseName(Sheet sheet, String cellValue, int currentColumn, int targetColumn) {
        String operateSteps = "";
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 0; i < rows; i++) {
            Row row = sheet.getRow(i);
            String cell = row.getCell(currentColumn).toString();
            if (cell.equals(cellValue)) {
                operateSteps = row.getCell(targetColumn).toString();
                break;
            }
        }
        return operateSteps;
    }



    private T getNewInstance() {
        try {
            return clazz.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
            throw new RuntimeException("目标对象不可创建，请确保您承接数据的类型不是接口或不是其他不可创建的类型。");
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException("创建目标对象实例发生错误，没有找到可访问的无参构造。");
        }
    }


    @Data
    @AllArgsConstructor
    class ExcelField {
        private Integer column;
        private Field field;

    }
}
